﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ProgNetComponents.Classes;
using ProgNetComponents.Collections;

namespace ProgNetComponents.Data.SqlCe
{
    /// <summary>
    /// SqlCeProcedure
    /// </summary>
    public class SqlCeProcedure : IDisposable
    {
        /// <summary>
        /// Creates SqlCeProcedure instance
        /// </summary>
        public SqlCeProcedure()
        {
            Manager = new SqlCeDataManager();
            KeyColumns = new List<string>();
            Columns = new NamedObjectCollection();
            SkipColumnsInInsert = new List<string>();
            SkipColumnsInUpdate = new List<string>();
            UpdateExisting = false;
        }

        /// <summary>
        /// Gets SqlCeDataManager
        /// </summary>
        public SqlCeDataManager Manager
        {
            get;
            private set;
        }
        /// <summary>
        /// Gets or sets if existing records should be updated
        /// </summary>
        public bool UpdateExisting
        {
            get;
            set;
        }
        /// <summary>
        /// Gets list of Key columns
        /// </summary>
        public List<string> KeyColumns
        {
            get;
            private set;
        }
        /// <summary>
        /// Gets all columns
        /// </summary>
        public NamedObjectCollection Columns
        {
            get;
            private set;
        }
        /// <summary>
        /// Gets or sets Table Name
        /// </summary>
        public string TableName
        {
            get;
            set;
        }
        /// <summary>
        /// Gets columns that should be skipped during update
        /// </summary>
        public List<string> SkipColumnsInUpdate
        {
            get;
            private set;
        }
        /// <summary>
        /// Gets columns that should be skipped during insert
        /// </summary>
        public List<string> SkipColumnsInInsert
        {
            get;
            private set;
        }
        /// <summary>
        /// Gets last exception
        /// </summary>
        public Exception LastException
        {
            get { return Manager.LastException; }
        }
        /// <summary>
        /// Gets Insert Query
        /// </summary>
        protected string InsertQuery
        {
            get;
            private set;
        }
        /// <summary>
        /// Gets Update Query
        /// </summary>
        protected string UpdateQuery
        {
            get;
            private set;
        }
        /// <summary>
        /// Gets Select Query
        /// </summary>
        protected string SelectCountQuery
        {
            get;
            private set;
        }
        /// <summary>
        /// Gets Where clause
        /// </summary>
        /// <returns></returns>
        protected virtual string GetWhereClause()
        {
            string where = string.Empty;
            foreach (var col in KeyColumns)
                where = where.AppendAnd() + col + " = @" + col;
            return where;
        }
        /// <summary>
        /// Creates Insert Query
        /// </summary>
        /// <returns></returns>
        protected virtual string GetInsertQuery()
        {
            string query = string.Empty;
            string insert = string.Empty;

            foreach (var col in Columns)
            {
                if (SkipColumnsInInsert.Contains(col.Name))
                    continue;
                query = query.AppendString(", ") + col.Name;
                insert = insert.AppendString(", ") + "@" + col.Name;
            }
            return "INSERT INTO " + TableName + "(" + query + " ) VALUES (" + insert + ")";
        }
        /// <summary>
        /// Creates Update query
        /// </summary>
        /// <returns></returns>
        protected virtual string GetUpdateQuery()
        {
            string query = string.Empty;

            foreach (var col in Columns)
            {
                if (SkipColumnsInUpdate.Contains(col.Name))
                    continue;
                if (KeyColumns.Contains(col.Name))
                    continue;

                query = query.AppendString(", ") + col.Name + " = @"+col.Name;
            }
            return "UPDATE " + TableName + " SET " + query + " WHERE " + GetWhereClause();
        }
        /// <summary>
        /// Creates Select query
        /// </summary>
        /// <returns></returns>
        protected virtual string GetSelectCountQuery()
        {
            return "SELECT COUNT(1) FROM " + TableName + " WHERE " + GetWhereClause();
        }
        /// <summary>
        /// Creates Insert, Update and Select query
        /// </summary>
        public virtual void InitQueries()
        {
            string where = GetWhereClause();
            InsertQuery = GetInsertQuery();
            UpdateQuery = GetUpdateQuery();
            SelectCountQuery = GetSelectCountQuery();
        }
        /// <summary>
        /// Gets parameters for Where clause
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected virtual NamedObject[] GetParametersForWhereClausule(params object[] parameters)
        {
            List<NamedObject> list = new List<NamedObject>();
            if (parameters != null)
            {
                NamedObject no;
                object val;
                foreach (var col in KeyColumns)
                {
                    no = Columns[col];
                    if (no != null)
                    {
                        val = no.Value;
                        if (parameters != null && parameters.Length > Columns.IndexOf(no))
                            val = parameters[Columns.IndexOf(no)];
                        
                        list.Add(new NamedObject("@"+col, val));
                    }
                    else
                    {
                        throw new Exception("Missing key column " + col);
                    }
                }
            }
            return list.ToArray();
        }
        /// <summary>
        /// Gets parameters for insert query
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected virtual NamedObject[] GetParametersForInsert(params object[] parameters)
        {
            List<NamedObject> list = new List<NamedObject>();
            if (parameters != null)
            {
                object val;
                foreach (var col in Columns)
                {
                    if (SkipColumnsInInsert.Contains(col.Name))
                        continue;
                    val = col.Value;
                    if (parameters != null && parameters.Length > Columns.IndexOf(col))
                        val = parameters[Columns.IndexOf(col)];

                    list.Add(new NamedObject("@" + col.Name, val));
                }
            }
            return list.ToArray();
        }
        /// <summary>
        /// Gets parameters for update query
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected virtual NamedObject[] GetParametersForUpdate(params object[] parameters)
        {
            List<NamedObject> list = new List<NamedObject>();
            if (parameters != null)
            {
                object val;
                foreach (var col in Columns)
                {
                    if (SkipColumnsInUpdate.Contains(col.Name))
                        continue;
                    if (KeyColumns.Contains(col.Name))
                        continue;
                    val = col.Value;
                    if (parameters != null && parameters.Length > Columns.IndexOf(col))
                        val = parameters[Columns.IndexOf(col)];

                    list.Add(new NamedObject("@" + col.Name, val));
                }
            }
            list.AddRange(GetParametersForWhereClausule(parameters));
            return list.ToArray();
        }
        /// <summary>
        /// Executoes procedure
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public bool Execute(params object[] parameters)
        {
            bool result = false;
            try
            {
                if(SelectCountQuery.IsNull())
                    InitQueries();

                int i = Manager.ExecuteScalar(SelectCountQuery, GetParametersForWhereClausule(parameters)).AsInt();
                if (i == 0)
                {
                    i = Manager.ExecuteNonQuery(InsertQuery, GetParametersForInsert(parameters));
                    result = i > 0;
                }
                else if (UpdateExisting)
                {
                    i = Manager.ExecuteNonQuery(UpdateQuery, GetParametersForUpdate(parameters));
                    result = i > 0;
                }
                else
                    result = true;
            }
            catch { result = false; }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        public virtual void ExecuteAll()
        {
            
        }

        private string GetWhere()
        {
            string where = string.Empty;
            foreach (var col in KeyColumns)
                where = where.AppendAnd() + col + " = @" + col;
            return where;
        }

        public void Dispose()
        {
            Manager.Dispose();
        }
    }
}
